package com.eeds.controller.pc;


import com.arjuna.ats.internal.jdbc.drivers.modifiers.list;
import com.eeds.common.enums.GearPositionEnum;
import com.eeds.common.excel.annotation.ExcelField;
import com.eeds.common.excel.annotation.ExcelSheet;
import com.eeds.model.work.res.ResExcelDetailedVO;
import com.eeds.model.work.res.ResExcelVO;
import com.eeds.model.work.res.ResGetWorkAttendanceInfoVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

@Slf4j
public class ExcelDownloadUtil {
    /**
     * 导出Excel对象
     *
     * @return
     */
    public static Workbook exportWorkbook(ResExcelVO resExcelVO) {




        // book
        // HSSFWorkbook=2003/xls、XSSFWorkbook=2007/xlsx
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("考勤");


        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeightInPoints(20);

        // sheet header row
        CellStyle headStyle = workbook.createCellStyle();

        Font font=workbook.createFont();
        font.setFontHeightInPoints((short) 16);
        font.setFontName("宋体");
        font.setBold(true);
        headStyle.setFont(font);
        createHead(sheet,headStyle);

        Row headRow = null;
        int rowNum = 2;
        for (ResExcelDetailedVO resExcelDetailedVO : resExcelVO.getResExcelDetailedList()) {
            headRow = sheet.createRow(rowNum);
            headRow.setHeight((short) (21.75 * 20));
            rowNum++;
            for (int i = 0; i < 13; i++) {

                Cell cellX = headRow.createCell(i, CellType.STRING);

                headStyle.setAlignment(HorizontalAlignment.CENTER);// 居中
                headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 居中

                headStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                headStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());

                headStyle.setBorderBottom(BorderStyle.THIN); // 下边框
                headStyle.setBorderLeft(BorderStyle.THIN);// 左边框
                headStyle.setBorderTop(BorderStyle.THIN);// 上边框
                headStyle.setBorderRight(BorderStyle.THIN);// 右边框
                headStyle.setWrapText(true);
                cellX.setCellStyle(headStyle);
                switch (i){
                    case 0:
                        sheet.setColumnWidth(cellX.getColumnIndex(),  (14 * 256));
                        cellX.setCellValue(resExcelDetailedVO.getUserName());
                        break;
                    case 1:
                        sheet.setColumnWidth(cellX.getColumnIndex(),  (20 * 256));
                        cellX.setCellValue(resExcelDetailedVO.getRoleName());
                        break;
                    case 2:
                        sheet.setColumnWidth(cellX.getColumnIndex(),  (12 * 256));
                        cellX.setCellValue(resExcelDetailedVO.getPostCoefficients());
                        break;
                    case 3:
                        sheet.setColumnWidth(cellX.getColumnIndex(),  (23 * 256));
                        cellX.setCellValue(resExcelDetailedVO.getAbsenteeismDays()+"/" + resExcelDetailedVO.getAttendanceDays());
                        break;
                    case 4:
                        sheet.setColumnWidth(cellX.getColumnIndex(),  (23 * 256));
                        cellX.setCellValue(resExcelDetailedVO.getAttendanceDays()/resExcelDetailedVO.getAttendanceDays());
                        break;
                    case 5:
                        sheet.setColumnWidth(cellX.getColumnIndex(),  (28 * 256));
                        cellX.setCellValue(resExcelDetailedVO.getAverageDistributionPerformanceBeforeAssessment());
                        break;
                    case 6:
                        sheet.setColumnWidth(cellX.getColumnIndex(),  (18 * 256));
                        cellX.setCellValue(resExcelDetailedVO.getAssessmentScore()/resExcelDetailedVO.getTotalScoreOfExamination());
                        break;
                    case 7:
                        sheet.setColumnWidth(cellX.getColumnIndex(),  (22 * 256));
                        cellX.setCellValue(resExcelDetailedVO.getTotalScoreProportionOfAssessment());
                        break;
                    case 8:
                        sheet.setColumnWidth(cellX.getColumnIndex(),  (15 * 256));
                        cellX.setCellValue(GearPositionEnum.A.getDescription()+"/"+GearPositionEnum.B.getDescription()+"/"+GearPositionEnum.C.getDescription()+"/");
                        break;
                    case 9:
                        sheet.setColumnWidth(cellX.getColumnIndex(),  (15 * 256));
                        cellX.setCellValue(GearPositionEnum.A.getSource()+"/"+GearPositionEnum.B.getSource()+"/"+GearPositionEnum.C.getSource()+"/");
                        break;
                    case 10:
                        sheet.setColumnWidth(cellX.getColumnIndex(),  (22 * 256));
                        cellX.setCellValue(resExcelDetailedVO.getGearPosition().getSource());
                        break;
                    case 11:
                        sheet.setColumnWidth(cellX.getColumnIndex(),  (26 * 256));
                        cellX.setCellValue(resExcelDetailedVO.getPerformanceDistributionAfterAssessment());
                        break;
                    case 12:
                        sheet.setColumnWidth(cellX.getColumnIndex(),  (16 * 256));
                        cellX.setCellValue(resExcelDetailedVO.getRemarks());
                        break;
                }
            }
        }
        return workbook;
    }


    private static void createHead(Sheet sheet,CellStyle headStyle){
        Row headRow0 = sheet.createRow(0);
        headRow0.setHeight((short) (37.5*20));

        for (int i = 0; i < 13; i++) {

            Cell cellX = headRow0.createCell(i, CellType.STRING);


            headStyle.setAlignment(HorizontalAlignment.CENTER);// 居中
            headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 居中

            headStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());

            headStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            headStyle.setBorderLeft(BorderStyle.THIN);// 左边框
            headStyle.setBorderTop(BorderStyle.THIN);// 上边框
            headStyle.setBorderRight(BorderStyle.THIN);// 右边框
            headStyle.setWrapText(true);
            cellX.setCellStyle(headStyle);
            switch (i){

                case 0:
                    cellX.setCellValue("客户服务中心外勤一班绩效考核总表");
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (14 * 256));
                    break;
                case 1:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (20 * 256));
                    break;
                case 2:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (12 * 256));
                    break;
                case 3:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (23 * 256));
                    break;
                case 4:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (23 * 256));
                    break;
                case 5:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (28 * 256));
                    break;
                case 6:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (18 * 256));
                    break;
                case 7:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (22 * 256));
                    break;
                case 8:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (15 * 256));
                    break;
                case 9:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (15 * 256));
                    break;
                case 10:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (22 * 256));
                    break;
                case 11:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (26 * 256));
                    break;
                case 12:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (16 * 256));
                    break;

            }
        }
        sheet.addMergedRegion(new CellRangeAddress(0, 0,0, 12));


        Row headRow = sheet.createRow(1);
        headRow.setHeight((short) (21.75 * 20));
        for (int i = 0; i < 13; i++) {

            Cell cellX = headRow.createCell(i, CellType.STRING);

            headStyle.setAlignment(HorizontalAlignment.CENTER);// 居中
            headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 居中

            headStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());

            headStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            headStyle.setBorderLeft(BorderStyle.THIN);// 左边框
            headStyle.setBorderTop(BorderStyle.THIN);// 上边框
            headStyle.setBorderRight(BorderStyle.THIN);// 右边框
            headStyle.setWrapText(true);
            cellX.setCellStyle(headStyle);
            switch (i){
                case 0:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (14 * 256));
                    cellX.setCellValue("姓名");
                    break;
                case 1:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (20 * 256));
                    cellX.setCellValue("岗位");
                    break;
                case 2:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (12 * 256));
                    cellX.setCellValue("岗位系数");
                    break;
                case 3:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (23 * 256));
                    cellX.setCellValue("出勤天数比例值");
                    break;
                case 4:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (23 * 256));
                    cellX.setCellValue("出勤比例");
                    break;
                case 5:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (28 * 256));
                    cellX.setCellValue("考核前平均分配绩效");
                    break;
                case 6:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (18 * 256));
                    cellX.setCellValue("考核总得分");
                    break;
                case 7:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (22 * 256));
                    cellX.setCellValue("考核总分比例");
                    break;
                case 8:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (15 * 256));
                    cellX.setCellValue("档位");
                    break;
                case 9:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (15 * 256));
                    cellX.setCellValue("档位倍数");
                    break;
                case 10:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (22 * 256));
                    cellX.setCellValue("实际档位倍数");
                    break;
                case 11:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (26 * 256));
                    cellX.setCellValue("考核后绩效分配");
                    break;
                case 12:
                    sheet.setColumnWidth(cellX.getColumnIndex(),  (16 * 256));
                    cellX.setCellValue("备注");
                    break;

            }

        }
    }


    /**
     * 导出Excel文件到磁盘
     *
     * @param resExcelVO
     * @param filePath
     */
    public static void exportToFile(ResExcelVO resExcelVO, String filePath) {
        // workbook
        Workbook workbook = exportWorkbook(resExcelVO);

        FileOutputStream fileOutputStream = null;
        try {
            // workbook 2 FileOutputStream
            fileOutputStream = new FileOutputStream(filePath);
            workbook.write(fileOutputStream);

            // flush
            fileOutputStream.flush();
        } catch (Exception e) {
            if(log.isErrorEnabled()) {
                log.error(e.getMessage(), e);
            }
            throw new RuntimeException(e);
        } finally {
            closeStream(fileOutputStream);
        }
    }

    /**
     * 导出Excel字节数据
     *
     * @return
     */
    public static byte[] exportToBytes(ResExcelVO resExcelVO) {
        // workbook
        Workbook workbook = exportWorkbook(resExcelVO);

        ByteArrayOutputStream byteArrayOutputStream = null;
        byte[] result = null;
        try {
            // workbook 2 ByteArrayOutputStream
            byteArrayOutputStream = new ByteArrayOutputStream();
            workbook.write(byteArrayOutputStream);

            // flush
            byteArrayOutputStream.flush();

            result = byteArrayOutputStream.toByteArray();
            return result;
        } catch (Exception e) {
            if(log.isErrorEnabled()) {
                log.error(e.getMessage(), e);
            }
            throw new RuntimeException(e);
        } finally {
            closeStream(byteArrayOutputStream);
        }
    }

    private static void closeStream(OutputStream outputStream) {
        try {
            if (outputStream != null) {
                outputStream.close();
            }
        } catch (Exception e) {
            if(log.isErrorEnabled()) {
                log.error(e.getMessage(), e);
            }
            throw new RuntimeException(e);
        }
    }


    /**
     * CSV文件生成方法
     *
     * @param dataList
     * @param dataList
     * @return
     */
    public static byte[]  exportCSVToBytes(List<?> dataList) {


        // data
        if (dataList == null || dataList.size() == 0) {
            throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data can not be empty.");
        }

        // sheet
        Class<?> sheetClass = dataList.get(0).getClass();
        ExcelSheet excelSheet = sheetClass.getAnnotation(ExcelSheet.class);

        // sheet field
        List<Field> fields = new ArrayList<Field>();
        if (sheetClass.getDeclaredFields() != null && sheetClass.getDeclaredFields().length > 0) {
            for (Field field : sheetClass.getDeclaredFields()) {
                if (Modifier.isStatic(field.getModifiers())) {
                    continue;
                }
                ExcelField excelField = field.getAnnotation(ExcelField.class);
                if (excelField == null) {
                    continue;
                }
                fields.add(field);
            }
        }

        if (fields == null || fields.size() == 0) {
            throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data field can not be empty.");
        }

        fields = fieldOrder(fields);

        BufferedWriter csvWtriter = null;
        try {

            ByteArrayOutputStream byteArrayOutputStream = null;
            byte[] result = null;
            // workbook 2 ByteArrayOutputStream
            byteArrayOutputStream = new ByteArrayOutputStream();

            // GB2312使正确读取分隔符","
            csvWtriter = new BufferedWriter(new OutputStreamWriter(byteArrayOutputStream, "GB2312"), 1024);

            // 写入文件头部
            writeRowHead(fields, csvWtriter);


            // 写入文件内容
            for (int dataIndex = 1; dataIndex < dataList.size(); dataIndex++) {
                Object rowData = dataList.get(dataIndex);
                writeRow(fields, rowData, csvWtriter);
            }
            csvWtriter.flush();
            return byteArrayOutputStream.toByteArray();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            try {
                csvWtriter.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 写一行数据方法
     * @param csvWriter
     * @throws IOException
     */
    private static void writeRow(List<Field> fields,Object obj, BufferedWriter csvWriter) {
        try {
            // 写入文件头部
            for (Field data : fields) {
                StringBuffer sb = new StringBuffer();

                data.setAccessible(true);
                Object fieldValue = data.get(obj) == null ? "" : data.get(obj);
                String rowStr = sb.append("\"").append(fieldValue).append("\",").toString();
                csvWriter.write(rowStr);
            }
            csvWriter.newLine();
        } catch (IllegalAccessException e){
            if(log.isErrorEnabled()) {
                log.error(e.getMessage(), e);
            }
            throw new RuntimeException(e);
        }catch (Exception e){
            if(log.isErrorEnabled()) {
                log.error(e.getMessage(), e);
            }
            throw new RuntimeException(e);
        }

    }


    /**
     * 写头方法
     * @param csvWriter
     * @throws IOException
     */
    private static void writeRowHead(List<Field> fields, BufferedWriter csvWriter) {
        try {
            // 写入文件头部
            for (Field data : fields) {
                StringBuffer sb = new StringBuffer();
                ExcelField excelField1 = data.getAnnotation(ExcelField.class);
                String rowStr = sb.append("\"").append(excelField1.name()).append("\",").toString();
                csvWriter.write(rowStr);
            }
            csvWriter.newLine();
        }catch (Exception e){
            if(log.isErrorEnabled()) {
                log.error(e.getMessage(), e);
            }
            throw new RuntimeException(e);
        }
    }


    private static List<Field> fieldOrder(List<Field> fields){
        Collections.sort(fields, new Comparator<Field>() {
            @Override
            public int compare(Field o1, Field o2) {

                ExcelField excelField1 = o1.getAnnotation(ExcelField.class);

                ExcelField excelField2 = o2.getAnnotation(ExcelField.class);

                int i = excelField1.orderNum() - excelField2.orderNum();
                if(i == 0){
                    return excelField1.orderNum();
                }
                return i;
            }
        });
        return fields;
    }

    public static void main(String[] args) {
        File file = new File("D:"+File.separator+"write.xlsx");
        OutputStream outputStream = null;
        if (!file.exists()) {
            try {
                // 如果文件找不到，就new一个
                file.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        try {
            // 定义输出流，写入文件的流
            outputStream = new FileOutputStream(file);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        // 定义将要写入文件的数据
        // 把string转换成byte型的，并存放在数组中
        byte[] bs = exportToBytes(null);
        try {
            // 写入bs中的数据到file中
            outputStream.write(bs);
        } catch (IOException e) {
            e.printStackTrace();
        }

        try {
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}
